In [1]:
import geopandas as gpd
from sqlalchemy import create_engine, text
from sqlalchemy_utils import create_database, database_exists, drop_database
import pandas as pd
import sqlalchemy

import json
from bokeh.io import show,output_notebook
from bokeh.models import (CDSView, ColorBar, ColumnDataSource,
                          CustomJS, CustomJSFilter, 
                          GeoJSONDataSource, HoverTool,
                          LinearColorMapper, LogColorMapper, Slider)
from bokeh.layouts import column, row, widgetbox
from bokeh.palettes import brewer
from bokeh.plotting import figure

Connect to Postgres DB

Load Dependancies

In [2]:
#Variables
db_type = "postgresql" #postgresql
username = "postgres"
password = "pass"
host = "localhost"
port = "5432"
db_name = "coviddistribution"

#Put it together
engine = create_engine(f"{db_type}://{username}:{password}@{host}:{port}/{db_name}")
In [3]:
inspector = sqlalchemy.inspect(engine)
inspector.get_table_names() #We see that our table was added to our database
Out[3]:
['spatial_ref_sys',
 'uscounties',
 'airports',
 'vaccine_locations',
 'covid_cases',
 'vaccination_county',
 'demographics_county']

Visualize Covid Cases per 100K

In [4]:
#SQL query
sql = """
SELECT
    covid_cases.county,
    covid_cases.state,
    covid_cases.cases,
    demographics_county."POPESTIMATE2019",
    uscounties.geometry as geometry
FROM
    covid_cases
INNER JOIN
    uscounties ON covid_cases.fips=CAST(uscounties."FIPS" AS BIGINT)
INNER JOIN
    demographics_county ON covid_cases.fips=demographics_county."FIPS"
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
covid_cases = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
# covid_cases = pd.read_sql(sql,engine)
In [5]:
covid_cases
Out[5]:
county state cases POPESTIMATE2019 geometry
0 Lake of the Woods Minnesota 34 3740 POLYGON ((-95.34283 48.54668, -95.34105 48.715...
1 Ferry Washington 30 7627 POLYGON ((-118.85163 47.94956, -118.84846 48.4...
2 Stevens Washington 207 45723 POLYGON ((-117.43883 48.04412, -117.54219 48.0...
3 Okanogan Washington 1091 42243 POLYGON ((-118.97209 47.93915, -118.97406 47.9...
4 Pend Oreille Washington 92 13724 POLYGON ((-117.43858 48.99992, -117.03205 48.9...
... ... ... ... ... ...
3116 Danville city Virginia 1011 40044 POLYGON ((-79.51977 36.59777, -79.50138 36.621...
3117 Yukon-Koyukuk Census Area Alaska 88 5230 POLYGON ((-161.04770 62.20469, -160.99428 62.8...
3118 Southeast Fairbanks Census Area Alaska 47 6893 POLYGON ((-146.96382 63.46070, -146.95735 64.2...
3119 Denali Borough Alaska 15 2097 POLYGON ((-152.98947 62.74900, -152.48773 63.1...
3120 Broomfield Colorado 716 70465 POLYGON ((-105.05201 39.99761, -104.99139 40.0...

3121 rows × 5 columns

In [6]:
covid_cases["cases_per_100k"] = (covid_cases["cases"] * 100000) / covid_cases["POPESTIMATE2019"]
covid_cases = covid_cases[(covid_cases["state"]!="Alaska") & (covid_cases["state"] != "Hawaii")]
covid_cases
Out[6]:
county state cases POPESTIMATE2019 geometry cases_per_100k
0 Lake of the Woods Minnesota 34 3740 POLYGON ((-95.34283 48.54668, -95.34105 48.715... 909.090909
1 Ferry Washington 30 7627 POLYGON ((-118.85163 47.94956, -118.84846 48.4... 393.339452
2 Stevens Washington 207 45723 POLYGON ((-117.43883 48.04412, -117.54219 48.0... 452.726199
3 Okanogan Washington 1091 42243 POLYGON ((-118.97209 47.93915, -118.97406 47.9... 2582.676420
4 Pend Oreille Washington 92 13724 POLYGON ((-117.43858 48.99992, -117.03205 48.9... 670.358496
... ... ... ... ... ... ...
3093 LaGrange Indiana 690 39614 POLYGON ((-85.66327 41.53557, -85.65946 41.762... 1741.808452
3094 Steuben Indiana 454 34594 POLYGON ((-84.79137 41.53049, -85.19715 41.537... 1312.366306
3115 Pittsylvania Virginia 1116 60354 POLYGON ((-79.71720 36.54803, -79.64135 36.846... 1849.090367
3116 Danville city Virginia 1011 40044 POLYGON ((-79.51977 36.59777, -79.50138 36.621... 2524.722805
3120 Broomfield Colorado 716 70465 POLYGON ((-105.05201 39.99761, -104.99139 40.0... 1016.107287

3098 rows × 6 columns

In [7]:
output_notebook()
Loading BokehJS ...
In [8]:
def get_geodatasource(gdf):    
    """Get getjsondatasource from geopandas object"""
    json_data = json.dumps(json.loads(gdf.to_json()))
    return GeoJSONDataSource(geojson = json_data)


def plot_choropleth(gdf,color_palette,column,title,tooltips=None,reverse_palette=True,use_min_color=None,use_max_color=None,add_vaccine=None,add_vaccine_df=None):
    geosource = get_geodatasource(gdf)
    palette = color_palette #brewer['YlOrBr'][8]
    
    if (reverse_palette):
        palette = palette[::-1]

    vals = gdf[column]

    if (not use_min_color):
        use_min_color = vals.min()
    if (not use_max_color):
        use_max_color = vals.max()
    
    color_mapper = LinearColorMapper(palette=palette, low=use_min_color, high = use_max_color)
    color_bar = ColorBar(color_mapper=color_mapper,label_standoff=8,width=500,height=20,orientation='horizontal')

    tools='wheel_zoom,pan,reset,save'
    p = figure(title=title,
               plot_height=500,
               plot_width=700,
               toolbar_location='left',
               tools=tools,
               output_backend="webgl")

    p.xgrid.grid_line_color = None
    p.ygrid.grid_line_color = None
    p.axis.visible = False

    covid_cases_plot = p.patches('xs','ys',
              source=geosource,
              fill_alpha=1,
              line_width=0.5,
              line_color='black',
              fill_color={'field':column,
                          'transform':color_mapper}
             )
    if (tooltips):
        # Create hover tool
        p.add_tools(HoverTool(renderers = [covid_cases_plot],
                              tooltips = tooltips))


    p.add_layout(color_bar,'below')
    
    if (add_vaccine!=None):
        vaccine_nn = add_vaccine_df
        sites = p.circle('x','y',source=ColumnDataSource({"x":vaccine_nn.geometry.x,"y":vaccine_nn.geometry.y,"icao":vaccine_nn["icao"],"dist":vaccine_nn["dist"],"siteid":vaccine_nn["siteid"],"vaccines_allocated_per_cvs":vaccine_nn["vaccines_allocated_per_cvs"]}),color='#0B0B0B',size=3,alpha=0.5,legend_label="Vaccination Sites")
        # Create hover tool
        p.add_tools(HoverTool(renderers = [sites],
                          tooltips = [('siteID',"@siteid"),("vaccines_allocated_cvs","@vaccines_allocated_per_cvs"),('Nearest Airport (Distribution Center)','@icao'),('Straight Line Distance in Miles','@dist')]))

    
    return p

show(plot_choropleth(covid_cases,
                     brewer['YlOrBr'][8],
                     "cases_per_100k",
                     "Covid Cases per 100K residents by County as of Oct. 8th 2021",
                     tooltips=[('County','@county'),
                               ('State','@state'),
                               ('Population','@POPESTIMATE2019'),
                               ('Cases','@cases'),
                               ('Cases Per 100K Residents','@cases_per_100k')
                              ]))

Visualize Minority Percentage by County

In [9]:
#SQL query
sql = """
SELECT
    uscounties."NAME" as county,
    uscounties."STATE_NAME" as state,
    demographics_county."POPESTIMATE2019" as population,
    demographics_county."Minority_Pct",
    uscounties.geometry as geometry
FROM
    demographics_county
INNER JOIN
    uscounties ON demographics_county."FIPS"=CAST(uscounties."FIPS" AS BIGINT)
WHERE
    uscounties."STATE_NAME"!='Alaska' AND
    uscounties."STATE_NAME"!='Hawaii'
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
minority_pct = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
In [10]:
minority_pct
Out[10]:
county state population Minority_Pct geometry
0 Lake of the Woods Minnesota 3740 0.066702 POLYGON ((-95.34283 48.54668, -95.34105 48.715...
1 Ferry Washington 7627 0.286988 POLYGON ((-118.85163 47.94956, -118.84846 48.4...
2 Stevens Washington 45723 0.153558 POLYGON ((-117.43883 48.04412, -117.54219 48.0...
3 Okanogan Washington 42243 0.344338 POLYGON ((-118.97209 47.93915, -118.97406 47.9...
4 Pend Oreille Washington 13724 0.118648 POLYGON ((-117.43858 48.99992, -117.03205 48.9...
... ... ... ... ... ...
3102 LaGrange Indiana 39614 0.053627 POLYGON ((-85.66327 41.53557, -85.65946 41.762...
3103 Steuben Indiana 34594 0.075301 POLYGON ((-84.79137 41.53049, -85.19715 41.537...
3104 Pittsylvania Virginia 60354 0.261632 POLYGON ((-79.71720 36.54803, -79.64135 36.846...
3105 Danville Virginia 40044 0.594694 POLYGON ((-79.51977 36.59777, -79.50138 36.621...
3106 Broomfield Colorado 70465 0.239880 POLYGON ((-105.05201 39.99761, -104.99139 40.0...

3107 rows × 5 columns

In [11]:
show(
    plot_choropleth(
        minority_pct,
        brewer['PuBu'][6],
        "Minority_Pct",
        "Percentage of Minorities by County",
        tooltips=[
            ('County','@county'),
            ('State','@state'),
            ('Population','@population'),
            ('Minority Percentage','@Minority_Pct')
        ]
    )
)

Visualize Vaccination Percentage By County

In [12]:
#SQL query
sql = """
SELECT
    uscounties."NAME" as county,
    uscounties."STATE_NAME" as state,
    demographics_county."POPESTIMATE2019" as population,
    demographics_county."Minority_Pct" as minority_pct,
    vaccination_county."Series_Complete_Pop_Pct" as vaccination_pct,
    uscounties.geometry as geometry
FROM
    vaccination_county
INNER JOIN
    uscounties ON vaccination_county."FIPS"=CAST(uscounties."FIPS" AS BIGINT)
INNER JOIN
    demographics_county ON vaccination_county."FIPS"=demographics_county."FIPS"

WHERE
    uscounties."STATE_NAME"!='Alaska' AND
    uscounties."STATE_NAME"!='Hawaii'
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
vac_cty = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
In [13]:
vac_cty
Out[13]:
county state population minority_pct vaccination_pct geometry
0 Lake of the Woods Minnesota 3740 0.066702 49.5 POLYGON ((-95.34283 48.54668, -95.34105 48.715...
1 Ferry Washington 7627 0.286988 48.1 POLYGON ((-118.85163 47.94956, -118.84846 48.4...
2 Stevens Washington 45723 0.153558 34.1 POLYGON ((-117.43883 48.04412, -117.54219 48.0...
3 Okanogan Washington 42243 0.344338 60.7 POLYGON ((-118.97209 47.93915, -118.97406 47.9...
4 Pend Oreille Washington 13724 0.118648 34.8 POLYGON ((-117.43858 48.99992, -117.03205 48.9...
... ... ... ... ... ... ...
3102 LaGrange Indiana 39614 0.053627 20.9 POLYGON ((-85.66327 41.53557, -85.65946 41.762...
3103 Steuben Indiana 34594 0.075301 43.6 POLYGON ((-84.79137 41.53049, -85.19715 41.537...
3104 Pittsylvania Virginia 60354 0.261632 38.0 POLYGON ((-79.71720 36.54803, -79.64135 36.846...
3105 Danville Virginia 40044 0.594694 33.3 POLYGON ((-79.51977 36.59777, -79.50138 36.621...
3106 Broomfield Colorado 70465 0.239880 70.6 POLYGON ((-105.05201 39.99761, -104.99139 40.0...

3107 rows × 6 columns

In [14]:
show(
    plot_choropleth(
        vac_cty,
        brewer['RdYlGn'][6],
        "vaccination_pct",
        "Percentage of Vaccinated Residents by County (Note 0% indicates non-reporting counties)",
        tooltips=[
            ('County','@county'),
            ('State','@state'),
            ('Population','@population'),
            ('Percent of Vaccinated Residents','@vaccination_pct')
        ],
        reverse_palette=True
    )
)

Distribution Network using a naive Nearest Neighbors algorithm (only consider airports within the same state)

In [15]:
#SQL query
sql = """
SELECT
    airports.icao,
    airports.name,
    airports.geometry AS geometry
FROM 
    airports
WHERE 
    airports.is_military=1
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
military_airports = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
In [16]:
military_airports
Out[16]:
icao name geometry
0 KAPG Phillips Army Air Field POINT (-76.16880 39.46620)
1 KBAB Beale Air Force Base POINT (-121.43700 39.13610)
2 KBAD Barksdale Air Force Base POINT (-93.66270 32.50180)
3 KBIF Biggs Army Air Field (Fort Bliss) POINT (-106.38000 31.84950)
4 KBIX Keesler Air Force Base POINT (-88.92440 30.41040)
... ... ... ...
61 KVAD Moody Air Force Base POINT (-83.19300 30.96780)
62 KVBG Vandenberg Air Force Base POINT (-120.58400 34.73730)
63 KWRB Robins Air Force Base POINT (-83.59190 32.64010)
64 KWRI Mc Guire Air Force Base POINT (-74.59170 40.01560)
65 US-0254 Yuma Auxiliary Army Airfield 2 POINT (-114.51179 32.54750)

66 rows × 3 columns

In [17]:
#SQL query
sql = """
SELECT
    airports.icao,
    airports.name,
    airports.geometry AS geometry
FROM 
    airports
WHERE 
    airports.is_military=0
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
non_military_airports = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
non_military_airports = non_military_airports[~(non_military_airports["name"].str.contains("Army")) & ~(non_military_airports["name"].str.contains("Navy")) & ~(non_military_airports["name"].str.contains("Air Force")) & ~(non_military_airports["name"].str.contains("Joint Base"))]
In [18]:
non_military_airports
Out[18]:
icao name geometry
0 KABQ Albuquerque International Sunport POINT (-106.60900 35.04020)
2 KAFW Fort Worth Alliance Airport POINT (-97.31880 32.98760)
3 KAGS Augusta Regional At Bush Field POINT (-81.96450 33.36990)
4 KAMA Rick Husband Amarillo International Airport POINT (-101.70600 35.21940)
5 KATL Hartsfield Jackson Atlanta International Airport POINT (-84.42810 33.63670)
... ... ... ...
160 KTRI Tri-Cities Regional TN/VA Airport POINT (-82.40740 36.47520)
161 KTUL Tulsa International Airport POINT (-95.88810 36.19840)
162 KTUS Tucson International Airport / Morris Air Nati... POINT (-110.93805 32.11500)
163 KTYS McGhee Tyson Airport POINT (-83.99400 35.81100)
165 KVPS Destin-Ft Walton Beach Airport POINT (-86.52540 30.48320)

137 rows × 3 columns

In [19]:
#SQL query
sql = """
SELECT
    DISTINCT ON (vaccine_locations."SiteID")
    airports.icao,
    vaccine_locations."SiteID" as siteid,
    vaccine_locations."STATE_NAME" as state,
    vaccine_locations."NAME" as county,
    vaccine_locations."FIPS" as fips,
    ABS(ST_Distance(ST_Transform(airports.geometry,3857),ST_Transform(vaccine_locations.geometry,3857)) * 0.0006213712) as dist,
    vaccine_locations.geometry as geometry
FROM 
    airports
INNER JOIN
    vaccine_locations ON
    airports.state_fips=vaccine_locations."STATE_FIPS"
WHERE 
    vaccine_locations."STATE_NAME"!='Alaska' AND
    vaccine_locations."STATE_NAME"!='Hawaii'
ORDER BY
    siteid ASC,
    dist ASC
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
vaccine_nn = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
In [20]:
vaccine_nn
Out[20]:
icao siteid state county fips dist geometry
0 KMIA 111 Florida Monroe 12087 54.672632 POINT (-80.44225 25.09272)
1 KMIA 112 Florida Miami-Dade 12086 26.604505 POINT (-80.44920 25.47729)
2 KMIA 113 Florida Miami-Dade 12086 26.812464 POINT (-80.46360 25.48047)
3 KMIA 114 Florida Miami-Dade 12086 24.944364 POINT (-80.43229 25.49424)
4 KMIA 115 Florida Miami-Dade 12086 24.955489 POINT (-80.44654 25.49991)
... ... ... ... ... ... ... ...
11264 KCOS 11580 Colorado Otero 8089 120.800239 POINT (-103.53000 37.78900)
11265 KDLF 11581 Texas Pecos 48371 164.965435 POINT (-102.47200 30.81200)
11266 KCOS 11582 Colorado Cheyenne 8017 130.438422 POINT (-102.83700 39.02800)
11267 KOFF 11583 Nebraska Lincoln 31111 325.349439 POINT (-100.61600 41.09900)
11268 KMIB 11584 North Dakota Divide 38023 147.630230 POINT (-103.40500 48.81500)

11269 rows × 7 columns

In [21]:
# Create figure object.
p = figure(title = 'US Counties, Vaccination Sites and Medium to Large Airports', 
           plot_height = 600 ,
           plot_width = 950, 
           toolbar_location = 'left',
           tools = "pan, wheel_zoom, reset, save",
           output_backend="webgl")
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None
# Add patch renderer to figure.
states = p.patches('xs','ys', source = get_geodatasource(minority_pct),
                   fill_color = '#171717',
                   line_color = 'gray', 
                   line_width = 0.25, 
                   fill_alpha = 1)

p.add_tools(HoverTool(renderers=[states],
                     tooltips = [('State','@state'),
                                 ('County','@county'),
                                 ('Population','@population')]))

sites = p.circle('x','y',source=ColumnDataSource({"x":vaccine_nn.geometry.x,"y":vaccine_nn.geometry.y,"icao":vaccine_nn["icao"],"dist":vaccine_nn["dist"],"siteid":vaccine_nn["siteid"]}),color='#FF4D2A',size=3,alpha=0.5,legend_label="Vaccination Sites")
# Create hover tool
p.add_tools(HoverTool(renderers = [sites],
                      tooltips = [('siteID',"@siteid"),('Nearest Airport (Distribution Center)','@icao'),('Straight Line Distance in Miles','@dist')]))


airports_military = p.circle('x','y',source=ColumnDataSource({"x":military_airports.geometry.x,"y":military_airports.geometry.y,"airportICAO":military_airports["icao"],"airportName":military_airports["name"]}),color="#04C4A1",size=8,alpha=0.7,legend_label="Military Airports")
# Create hover tool
p.add_tools(HoverTool(renderers = [airports_military],
                      tooltips = [('Airport Name','@airportName'),('Airport ICAO','@airportICAO')]))

airports_non_military = p.circle('x','y',source=ColumnDataSource({"x":non_military_airports.geometry.x,"y":non_military_airports.geometry.y,"airportICAO":non_military_airports["icao"],"airportName":non_military_airports["name"]}),color="#FFE98A",size=5,alpha=0.7,legend_label="Non-Military Airports")
# Create hover tool
p.add_tools(HoverTool(renderers = [airports_non_military],
                      tooltips = [('Airport Name','@airportName'),('Airport ICAO','@airportICAO')]))


p.legend.location = "bottom_right"
p.legend.click_policy="hide"

p.background_fill_color = "#161616"
p.background_fill_alpha = 0.9
p.axis.visible = False

show(p)

Visualizing Estimated Number of Vaccines Needed

Assumptions Made:

  1. Infected Residents are immune to COVID-19 (i.e no intersection between vaccinated residents and previously infected residents)
  2. Since Texas Doesn't report county level vaccination data, assume the average national county vaccination percentage for non-minority majority counties and minority majority counties for all the respective counties in Texas.
  3. Since vaccines are finite, only 70% of the result of allocated_vaccines = pop - infected - vaccinated will be the true allocated vaccine count.
  4. If the value is negative, floor to zero
In [22]:
#SQL query
sql = """
SELECT
    uscounties."NAME" as county,
    uscounties."STATE_NAME" as state,
    CAST(uscounties."FIPS" AS BIGINT) as fips,
    demographics_county."POPESTIMATE2019" as population,
    demographics_county."Minority_Pct" as minority_pct,
    
    CAST(FLOOR((demographics_county."POPESTIMATE2019" - vaccination_county."Series_Complete_Yes" - covid_cases.cases) * 0.7) AS INT) as vaccines_allocated,
    uscounties.geometry as geometry
FROM
    vaccination_county
INNER JOIN
    uscounties ON vaccination_county."FIPS"=CAST(uscounties."FIPS" AS BIGINT)
INNER JOIN
    demographics_county ON vaccination_county."FIPS"=demographics_county."FIPS"
INNER JOIN
    covid_cases ON vaccination_county."FIPS"=covid_cases.fips
WHERE
    uscounties."STATE_NAME"!='Alaska' AND
    uscounties."STATE_NAME"!='Hawaii'
"""

#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"

#Execute query to create GeoDataFrame
allocated_vaccines = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
In [23]:
allocated_vaccines["vaccines_allocated"] = allocated_vaccines["vaccines_allocated"].clip(lower=0)
In [24]:
national_non_minmaj_vac_pct = vac_cty[vac_cty["minority_pct"]<0.5]["vaccination_pct"].mean() / 100
national_minmaj_vac_pct = vac_cty[vac_cty["minority_pct"]>=0.5]["vaccination_pct"].mean() / 100
In [25]:
allocated_vaccines.loc[(allocated_vaccines["state"]=="Texas") & (allocated_vaccines["minority_pct"] < 0.5),"vaccines_allocated"] *= (1-national_non_minmaj_vac_pct) * 0.7
allocated_vaccines.loc[(allocated_vaccines["state"]=="Texas") & (allocated_vaccines["minority_pct"] >= 0.5),"vaccines_allocated"] *= (1-national_minmaj_vac_pct) * 0.7

allocated_vaccines["vaccines_allocated"] = allocated_vaccines["vaccines_allocated"].astype(int)

allocated_vaccines[(allocated_vaccines["state"]=="Texas")]
Out[25]:
county state fips population minority_pct vaccines_allocated geometry
1840 Lipscomb Texas 48295 3233 0.356326 950 POLYGON ((-100.00727 36.49391, -100.00155 36.4...
1841 Sherman Texas 48421 3022 0.420561 882 POLYGON ((-102.16803 36.05466, -102.16567 36.4...
1843 Dallam Texas 48111 7287 0.454814 2090 POLYGON ((-102.16803 36.05466, -103.02405 36.0...
1844 Hansford Texas 48195 5399 0.385430 1560 POLYGON ((-101.61994 36.05475, -101.62076 36.4...
1853 Ochiltree Texas 48357 9836 0.439041 2884 POLYGON ((-101.08275 36.05630, -101.09010 36.4...
... ... ... ... ... ... ... ...
2900 Kenedy Texas 48261 404 0.608571 116 MULTIPOLYGON (((-97.96403 26.61310, -97.99179 ...
2904 Starr Texas 48427 64633 0.668704 17978 POLYGON ((-98.60027 26.26080, -98.67822 26.242...
2905 Hidalgo Texas 48215 868707 0.654845 247057 POLYGON ((-98.60027 26.26080, -98.34157 26.787...
2907 Willacy Texas 48489 21358 0.566405 5955 MULTIPOLYGON (((-97.86923 26.34870, -97.87389 ...
2909 Cameron Texas 48061 423163 0.613538 118260 MULTIPOLYGON (((-97.25150 26.41965, -97.23236 ...

252 rows × 7 columns

In [26]:
cvs_counts = pd.DataFrame(vaccine_nn["fips"].value_counts())
cvs_counts["counts"] =cvs_counts["fips"]
cvs_counts["fips"] = cvs_counts.index
# cvs_counts
cvs_counts = pd.merge(cvs_counts,vaccine_nn,on="fips")
cvs_counts = pd.merge(cvs_counts,allocated_vaccines,on="fips")
cvs_counts["vaccines_allocated_per_csv"] = round(cvs_counts["vaccines_allocated"] / cvs_counts["counts"])
cvs_counts = cvs_counts[["siteid","icao","dist","state_x","county_x","fips","population","minority_pct","vaccines_allocated_per_csv","geometry_x"]]
cvs_counts.columns = ["siteid","icao","dist","state","county","fips","population","minory_pct","vaccines_allocated_per_cvs","geometry"]
cvs_counts = gpd.GeoDataFrame(cvs_counts)
cvs_counts
Out[26]:
siteid icao dist state county fips population minory_pct vaccines_allocated_per_cvs geometry
0 3078 KSLI 17.414141 California Los Angeles 6037 10039107 0.674513 7415.0 POINT (-118.29265 33.72853)
1 3087 KLAX 18.347683 California Los Angeles 6037 10039107 0.674513 7415.0 POINT (-118.31643 33.73573)
2 3092 KSLI 17.168036 California Los Angeles 6037 10039107 0.674513 7415.0 POINT (-118.29206 33.73759)
3 3123 KSLI 4.972602 California Los Angeles 6037 10039107 0.674513 7415.0 POINT (-118.11257 33.75782)
4 3137 KLAX 14.715942 California Los Angeles 6037 10039107 0.674513 7415.0 POINT (-118.36637 33.76924)
... ... ... ... ... ... ... ... ... ... ...
11080 1768 KHLR 55.174142 Texas Milam 48331 24823 0.302699 7253.0 POINT (-96.98803 30.85636)
11081 4897 KEND 62.067045 Oklahoma Payne 40119 81784 0.257110 31342.0 POINT (-97.05871 36.12677)
11082 11445 KSKF 92.040349 Texas La Salle 48283 7520 0.521459 2116.0 POINT (-99.19200 28.34900)
11083 11388 KBIF 199.193295 Texas Jeff Davis 48243 2274 0.265030 674.0 POINT (-103.79000 30.77400)
11084 7823 KCMH 74.951415 Ohio Coshocton 39031 36600 0.051404 16699.0 POINT (-81.86890 40.27110)

11085 rows × 10 columns

In [27]:
show(
    plot_choropleth(
        allocated_vaccines,
        brewer['RdYlGn'][11],
        "vaccines_allocated",
        "Allocated Vaccines Per County",
        tooltips=[
            ('County','@county'),
            ('State','@state'),
            ('Population','@population'),
            ('Minority Percent','@minority_pct'),
            ('Vaccines Allocated','@vaccines_allocated')
        ],
        use_min_color=0,
        reverse_palette=False,
        use_max_color=50000,
        add_vaccine=True,
        add_vaccine_df=cvs_counts
    )
)
In [ ]: